package com.zyc.app.woa.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.zyc.app.woa.data.AccountInfo;
import com.zyc.app.woa.data.CachedArticle;
import com.zyc.app.woa.utils.AsyncTaskExecutor;
import com.zyc.app.woa.utils.StorageUtils;
import org.apache.commons.io.FileUtils;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;

/**
 * Author: yingchang.zhang
 * Date  : 2014/12/15
 */
public class DBManager {
    private static DBHelper sDbHelper;
    private static SQLiteDatabase sDb;
    private static ArrayList<DBObserver> sArticleObservers = new ArrayList<DBObserver>();
    private static ArrayList<DBObserver> sAccountObservers = new ArrayList<DBObserver>();

    public static interface DBObserver {
        public void onDelete(Object data);

        public void onAdd(Object data);
    }

    public static void registerDownloadListener(String tableName, DBObserver observer) {
        if (AccountTable.NAME.equals(tableName)) {
            sAccountObservers.add(observer);
        } else if (ArticlesTable.NAME.equals(tableName)) {
            sArticleObservers.add(observer);
        }
    }

    public static void unregisterDownloadListener(String tableName, DBObserver observer) {
        if (AccountTable.NAME.equals(tableName)) {
            sAccountObservers.remove(observer);
        } else if (ArticlesTable.NAME.equals(tableName)) {
            sArticleObservers.remove(observer);
        }
    }

    private static void performDBDelete(String tableName, Object obj) {
        if (AccountTable.NAME.equals(tableName)) {
            notifyDelete(sAccountObservers, obj);
        } else if (ArticlesTable.NAME.equals(tableName)) {
            notifyDelete(sArticleObservers, obj);
        }
    }

    private static void performDBAdd(String tableName, Object obj) {
        if (AccountTable.NAME.equals(tableName)) {
            notifyAdd(sAccountObservers, obj);
        } else if (ArticlesTable.NAME.equals(tableName)) {
            notifyAdd(sArticleObservers, obj);
        }
    }

    private static void notifyDelete(ArrayList<DBObserver> observers, Object obj) {
        for (DBObserver listener : observers) {
            if (null != listener) {
                listener.onDelete(obj);
            }
        }
    }

    private static void notifyAdd(ArrayList<DBObserver> observers, Object obj) {
        for (DBObserver listener : observers) {
            if (null != listener) {
                listener.onAdd(obj);
            }
        }
    }

    public static void init(Context context) {
        sDbHelper = new DBHelper(context);
        sDb = sDbHelper.getWritableDatabase();
    }

    public static void addAccount(AccountInfo accountInfo) {
        insertAccount(accountInfo);
        performDBAdd(AccountTable.NAME, accountInfo);
    }

    public static void removeAccount(AccountInfo accountInfo) {
        deleteAccount(accountInfo.wxId);
        performDBDelete(AccountTable.NAME, accountInfo);
    }

    public static ArrayList<AccountInfo> getAccounts() {
        Cursor cursor = queryAccounts();
        return buildAccountList(cursor);
    }

    public static String getTextFromCursor(Cursor cursor, String columnName) {
        String text = null;
        try {
            text = cursor.getString(cursor.getColumnIndex(columnName));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return text;
    }

    public static ArrayList<CachedArticle> getCachedArticles(String wxId) {
        Cursor cursor = getArticles(wxId);
        return buildArticlesList(cursor);
    }

    public static void removeCachedArticles(String wxId) {
        String path = StorageUtils.getAccountArticlesCachePath(wxId);
        File filePath = new File(path);
        if (filePath.exists()) {
            try {
                FileUtils.deleteDirectory(filePath);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void deleteArticle(final CachedArticle article) {
        AsyncTaskExecutor.execute(new Runnable() {
            @Override
            public void run() {
                deleteArticleFromDb(article.cacheFile);
                File htmlFile = new File(article.cacheFile);
                File articlePath = htmlFile.getParentFile();
                try {
                    FileUtils.deleteDirectory(articlePath);
                } catch (IOException e) {
                    e.printStackTrace();
                }
                performDBDelete(ArticlesTable.NAME, article);
            }
        });
    }

    public static ArrayList<CachedArticle> getReadedArticles() {
        return buildArticlesList(getReadedArticlesCursor());
    }

    private static ArrayList<AccountInfo> buildAccountList(Cursor cursor) {
        ArrayList<AccountInfo> accounts = new ArrayList<AccountInfo>();
        if (null != cursor) {
            if (cursor.moveToFirst()) {
                do {
                    accounts.add(makeAccountInfo(cursor));
                } while (cursor.moveToNext());
            }

            cursor.close();
        }
        return accounts;
    }

    private static ArrayList<CachedArticle> buildArticlesList(Cursor cursor) {
        ArrayList<CachedArticle> articles = new ArrayList<CachedArticle>();
        if (null != cursor) {
            if (cursor.moveToFirst()) {
                do {
                    articles.add(makeCacheArticle(cursor));
                } while (cursor.moveToNext());
            }
            cursor.close();
        }
        return articles;
    }

    public static void updateArticleReadStatus(CachedArticle cachedArticle, boolean read) {
        if (null != cachedArticle) {
            cachedArticle.read = read ? ArticlesTable.COLUMN_VALUE_READ : ArticlesTable.COLUMN_VALUE_NONE;
            updateArticleReadStatus(cachedArticle);
        }
    }

    public static void updateArticleFavoriteStatus(CachedArticle cachedArticle, boolean favorite) {
        if (null != cachedArticle) {
            cachedArticle.favorite = favorite ? ArticlesTable.COLUMN_VALUE_FAVORITE : ArticlesTable.COLUMN_VALUE_NONE;
            updateArticleFavoriteStatus(cachedArticle);
        }
    }

    private static AccountInfo makeAccountInfo(Cursor cursor) {
        AccountInfo account = new AccountInfo();
        account.name = getTextFromCursor(cursor, AccountTable.COLUMN_NAME);
        account.wxId = getTextFromCursor(cursor, AccountTable.COLUMN_WX_ID);
        account.desc = getTextFromCursor(cursor, AccountTable.COLUMN_DESC);
        account.href = getTextFromCursor(cursor, AccountTable.COLUMN_HREF);
        account.avatar = getTextFromCursor(cursor, AccountTable.COLUMN_AVATAR);
        account.openId = getTextFromCursor(cursor, AccountTable.COLUMN_OPEN_ID);
        account.updateTimeStamp = getLongFromCursor(cursor, AccountTable.COLUMN_UPDATE_TIME);
        account.subscribeArticlesNum = getIntegerFromCursor(cursor, AccountTable.COLUMN_SUBSCRIBE_ARTICLES_NUM);
        account.totalArticlesNum = getArticleCount(account.wxId);
        return account;
    }

    private static CachedArticle makeCacheArticle(Cursor cursor) {
        CachedArticle article = new CachedArticle();
        article.id = getIntegerFromCursor(cursor, ArticlesTable.COLUMN_ID);
        article.title = getTextFromCursor(cursor, ArticlesTable.COLUMN_TITLE);
        article.wxId = getTextFromCursor(cursor, ArticlesTable.COLUMN_WX_ID);
        article.cachedTime = getLongFromCursor(cursor, ArticlesTable.COLUMN_CACHE_TIME);
        article.imgLink = getTextFromCursor(cursor, ArticlesTable.COLUMN_IMG_LINK);
        article.url = getTextFromCursor(cursor, ArticlesTable.COLUMN_MSG_URL);
        article.cacheFile = getTextFromCursor(cursor, ArticlesTable.COLUMN_CACHED_FILE);
        article.readTime = getLongFromCursor(cursor, ArticlesTable.COLUMN_READ_TIME);
        article.read = getIntegerFromCursor(cursor, ArticlesTable.COLUMN_READ);
        article.favorite = getIntegerFromCursor(cursor, ArticlesTable.COLUMN_FAVORITE);
        return article;
    }

    private static int getArticleCount(String wxId) {
        Cursor cursor = sDb.rawQuery("SELECT COUNT (*) FROM " + ArticlesTable.NAME + " WHERE " +
                ArticlesTable.COLUMN_WX_ID + "=?", new String[]{wxId});

        int count = 0;
        if (null != cursor) {
            if (cursor.moveToFirst()) {
                count = cursor.getInt(0);
            }
            cursor.close();
        }
        return count;
    }

    public static long getLongFromCursor(Cursor cursor, String columnName) {
        long num = 0L;
        try {
            num = cursor.getLong(cursor.getColumnIndex(columnName));
        } catch (Exception e) {
            e.printStackTrace();
        }

        return num;
    }

    public static int getIntegerFromCursor(Cursor cursor, String columnName) {
        int num = 0;
        try {
            num = cursor.getInt(cursor.getColumnIndex(columnName));
        } catch (Exception e) {
            e.printStackTrace();
        }

        return num;
    }

    public static void updateAccountsTime(AccountInfo accountInfo, long timestamp) {
        ContentValues values = new ContentValues();
        values.put(AccountTable.COLUMN_UPDATE_TIME, timestamp);
        sDb.update(AccountTable.NAME, values, AccountTable.COLUMN_WX_ID + "=?", new String[]{accountInfo.wxId});
    }

    public static void insertArticle(CachedArticle article) {
        sDb.execSQL("INSERT INTO " + ArticlesTable.NAME + "(" +
                        ArticlesTable.COLUMN_TITLE + ", " +
                        ArticlesTable.COLUMN_WX_ID + ", " +
                        ArticlesTable.COLUMN_IMG_LINK + ", " +
                        ArticlesTable.COLUMN_MSG_URL + ", " +
                        ArticlesTable.COLUMN_CACHED_FILE + ", " +
                        ArticlesTable.COLUMN_CACHE_TIME + ", " +
                        ArticlesTable.COLUMN_READ_TIME + ", " +
                        ArticlesTable.COLUMN_READ + ", " +
                        ArticlesTable.COLUMN_FAVORITE +
                        ")" +
                        " VALUES " +
                        "(" + "?, " + "?, " + "?, " + "?, " + "?, " + "?, " + "?, " + "?, " + "?" + ")",
                new Object[]{article.title, article.wxId, article.imgLink, article.url, article.cacheFile,
                        article.cachedTime, article.readTime, article.read, article.favorite});
        performDBAdd(ArticlesTable.NAME, article);
    }

    public static void deleteArticleFromDb(String path) {
        sDb.execSQL("DELETE FROM " + ArticlesTable.NAME + " WHERE " + ArticlesTable.COLUMN_CACHED_FILE + "=?", new String[]{path});
    }

    public static void updateArticleReadStatus(CachedArticle article) {
        ContentValues values = new ContentValues();
        values.put(ArticlesTable.COLUMN_READ, article.read);
        values.put(ArticlesTable.COLUMN_READ_TIME, article.readTime);
        sDb.update(ArticlesTable.NAME, values, ArticlesTable.COLUMN_ID + "=?", new String[]{String.valueOf(article.id)});
    }

    public static void updateArticleFavoriteStatus(CachedArticle article) {
        ContentValues values = new ContentValues();
        values.put(ArticlesTable.COLUMN_FAVORITE, article.favorite);
        sDb.update(ArticlesTable.NAME, values, ArticlesTable.COLUMN_ID + "=?", new String[]{String.valueOf(article.id)});
    }

    public static Cursor getArticles(String wxId) {
        return sDb.rawQuery("SELECT * FROM " + ArticlesTable.NAME + " WHERE " + ArticlesTable.COLUMN_WX_ID + "=?", new String[]{wxId});
    }

    public static int getTotalArticleCount() {
        int count = 0;
        Cursor cursor = sDb.rawQuery("SELECT count(*) from " + ArticlesTable.NAME, null);
        if (null != cursor) {
            if (cursor.moveToFirst()) {
                count = cursor.getInt(0);
            }
            cursor.close();
        }

        return count;
    }

    private static Cursor getReadedArticlesCursor() {
        return sDb.rawQuery("SELECT * FROM " + ArticlesTable.NAME + " WHERE " + ArticlesTable.COLUMN_READ + "=?", new String[]{String.valueOf(ArticlesTable.COLUMN_VALUE_READ)});
    }

    private static Cursor queryAccounts() {
        return sDb.query(AccountTable.NAME, null, null, null, null, null, null);
    }

    private static void insertAccount(AccountInfo accountInfo) {
        sDb.execSQL("INSERT INTO " + AccountTable.NAME +
                        "(" + AccountTable.COLUMN_NAME + ", " +
                        AccountTable.COLUMN_OPEN_ID + ", " +
                        AccountTable.COLUMN_WX_ID + ", " +
                        AccountTable.COLUMN_DESC + ", " +
                        AccountTable.COLUMN_AVATAR + ", " +
                        AccountTable.COLUMN_HREF + ", " +
                        AccountTable.COLUMN_UPDATE_TIME + ", " +
                        AccountTable.COLUMN_SUBSCRIBE_ARTICLES_NUM +
                        ")" +
                        " VALUES " +
                        "(" + "?, " + "?, " + "?, " + "?, " + "?, " + "?, " + "?, " + "? " + ")",
                new Object[]{accountInfo.name, accountInfo.openId, accountInfo.wxId, accountInfo.desc,
                        accountInfo.avatar, accountInfo.href, accountInfo.updateTimeStamp, accountInfo.subscribeArticlesNum});
    }

    private void updateAccount(AccountInfo accountInfo) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(AccountTable.COLUMN_HREF, accountInfo.href);
        contentValues.put(AccountTable.COLUMN_AVATAR, accountInfo.avatar);
        contentValues.put(AccountTable.COLUMN_DESC, accountInfo.desc);
        sDb.update(AccountTable.NAME, contentValues, AccountTable.COLUMN_WX_ID + "=?", new String[]{accountInfo.wxId});
    }

    private static void deleteAccount(String wxId) {
        sDb.execSQL("DELETE FROM " + AccountTable.NAME + " WHERE " + AccountTable.COLUMN_WX_ID + "=?", new String[]{wxId});
        sDb.execSQL("DELETE FROM " + ArticlesTable.NAME + " WHERE " + ArticlesTable.COLUMN_WX_ID + "=?" +
                " AND " + ArticlesTable.COLUMN_FAVORITE + "=?", new String[]{wxId, String.valueOf(ArticlesTable.COLUMN_VALUE_FAVORITE)});
    }

}
